package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import kr.hkit.powerjava.chapter26.common.ConnectionFactory;
import kr.hkit.powerjava.chapter26.common.DbUtil;
import kr.hkit.powerjava.chapter26.dto.Department;

public class DepartmentDAO implements DaoInterface<Department>{
	//정보를 가지고 있어야 쿼리를 보낸다.
	private Connection connection;
	private PreparedStatement pstmt;		//궂이 필드로 선언할 필요는 없다.
	
	public DepartmentDAO(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert into department values(?, ?, ?)";
//		connection = ConnectionFactory.getConnection(); 
		pstmt = connection.prepareStatement(sql);
		//? 에 대한 값을 넣어준다.
		pstmt.setInt(1, item.getDeptNo());
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(3, item.getFloor());
		pstmt.executeUpdate();
		/*pstmt.close();
		connection.close(); 밑에 하나로 정의*/
		DbUtil.close(pstmt);
//		DbUtil.close(pstmt, connection);
//		System.out.println(pstmt);
//		System.out.println(res);
	}

	@Override
	public void deleteItemByNo(Department item) throws SQLException {
		String sql = "delete from department where deptno = ?";	//sql문
//		connection = ConnectionFactory.getConnection();  //연결
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		//System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItemByNo(Department item) throws SQLException {
		String sql = "update department set deptname=?, floor=? where deptno=?";
//		connection = ConnectionFactory.getConnection();  //연결
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptNo());
		//System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list = new ArrayList<>();
		String sql = "select deptno, deptname, floor from department";
//		connection = ConnectionFactory.getConnection();  //연결
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();//select 문은 executeUpdate가 아닌 executeUpdate가 온다.
		while(rs.next()){
			//add 시 index를 적어주어도 상관없다 ex)rs.getInt("deptno") == rs.getInt(1)
			list.add(new Department(rs.getInt("deptno"), rs.getString("deptname"), rs.getInt("floor")));
			
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
//		DbUtil.close(rs, pstmt, connection);
		return list;
	}

	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department = null;
		String sql = "select deptno, deptname, floor from department where deptno =?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			department = new Department();
			department.setDeptNo(rs.getInt(1));
			department.setDeptName(rs.getString(2));
			department.setFloor(rs.getInt(3));
		}
		return department;
	}
	
}
